package com.dosh;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import java.math.BigInteger;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;

/**
 * The DBAdsapter class creates and manages the database. 
 * It supports functions to create, delete or change entries
 */
public class DBAdapter {

private static final String DATABASE_NAME = "Dosh-Database";
private static final int DATABASE_VERSION = 2;
private static final String TABLE_USER ="UserA";
private static final String TABLE_BOOKING ="Booking";
private static final String TABLE_PLANNING ="Planning";
private static final String TABLE_CATEGORY ="Category";
private static final String TABLE_BUDGET ="Budget";
private static final String TABLE_ITEM ="Item";
private static final String TABLE_BOOKING_ITEM ="Booking_Item";
private static final String TABLE_JOB = "Job";
private static final String TABLE_JOBDONE = "Job_Done";


private final Context context;

private DatabaseHelper DBHelper;
private SQLiteDatabase db;

public DBAdapter(Context ctx)
{
this.context = ctx;
DBHelper = new DatabaseHelper(context);
}
/**
 * The DatabaseHelper is needed to configure the complete database
 */
private static class DatabaseHelper extends SQLiteOpenHelper
{
	DatabaseHelper(Context context)
	{
	super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}
	/**
	 * All tables are created, if they don't exist already
	 */
	@Override
	public void onCreate(SQLiteDatabase db)
	{
	try {
	db.execSQL("create table if not exists UserA(User_ID integer primary key autoincrement, "
			+ "Firstname TEXT not null, Surname TEXT not null, Username TEXT not null, "
			+ "Password TEXT not null, RegisterDate TEXT);");
	
	db.execSQL("create table if not exists Budget(Budget_ID integer primary key autoincrement, "
			+ "Name VARCHAR not null, Amount integer not null, Description VARCHAR, "
			+ "Reason VARCHAR, User_ID long, Actual_Amount long, "
			+ "foreign key(User_ID) references UserA(User_ID) on delete cascade);");
	
	db.execSQL("create table if not exists Booking(Booking_ID integer primary key autoincrement, "
			+ "Amount integer not null, Description VARCHAR);");
	
	db.execSQL("create table if not exists Category (Category_ID integer primary key autoincrement, "
			+ "Name TEXT not null, User_ID long, "
			+ "foreign key(User_ID) references UserA(User_ID) on delete set null);");
	
	db.execSQL("create table if not exists Item (Item_ID integer primary key autoincrement, "
			+ "Name VARCHAR not null, Category long, "
			+ "foreign key (Category) references Category(Category_ID) on delete set null);");
	
	db.execSQL("create table if not exists Booking_Item (Booking_Item_ID integer primary key autoincrement, "
			+ "Item long, Booking long, "
			+ "foreign key (Item) references Item(Item_ID) on delete cascade, "
			+ "foreign key (Booking) references Booking(Booking_ID) on delete cascade);");
	
	db.execSQL("create table if not exists Planning (Planning_ID integer primary key autoincrement, "
			+ "Booking long, Budget long, Cycle long, Starttime TEXT not null, Count integer,"
			+ "foreign key(Booking) references Booking(Booking_ID) on delete cascade, "
			+ "foreign key(Budget) references Budget(Budget_ID) on delete cascade);");
	
	db.execSQL("create table if not exists Job (Job_ID integer primary key autoincrement, "
			+ "Expiring VARCHAR, Amount long, Planning long,"
			+ "foreign key(Planning) references Planning(Planning_ID) on delete cascade);");
	
	db.execSQL("create table if not exists Job_Done (Job_Done_ID integer primary key autoincrement, "
			+ "Expiring VARCHAR, Amount long, Planning long,"
			+ "foreign key(Planning) references Planning(Planning_ID) on delete cascade);");
	
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	/**
	 * The complete Database gets an upgrade
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
	{

	}
}

/**
 * Opens the database
 */
public DBAdapter open() throws SQLException
{
db = DBHelper.getWritableDatabase();
return this;
}

/**
 * Closes the database
 */
public void close()
{
DBHelper.close();
}
/**
 * Executes a 'INSERT' statement
 */
public void sql(String s){
	try {
		db.execSQL(s);
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
}
/**
 * Executes a 'SELECT' statement
 * @return Returns a cursor to the first element of the result
 */
public Cursor rawQuery(String s){
	Cursor tCursor =
		db.rawQuery(s, null);
		if (tCursor.moveToFirst()) {
			return tCursor;
		}
		return null;
}

/**
 * This method encrypts the input
 * @return Returns the encrypted string
 */
public static String md5(String input) {
    
    String md5 = null;
     
    if(null == input) return null;
     
    try {
         
    //Create MessageDigest object for MD5
    MessageDigest digest = MessageDigest.getInstance("MD5");
     
    //Update input string in message digest
    digest.update(input.getBytes(), 0, input.length());

    //Converts message digest value in base 16 (hex) 
    md5 = new BigInteger(1, digest.digest()).toString(16);

    } catch (NoSuchAlgorithmException e) {

        e.printStackTrace();
    }
    return md5;
}

/**
 * Inserts a User entry into the User table
 * @return Returns the User_ID
 */
public long insertUser(String firstname, String surname, String username, String password, String registerdate)
{
ContentValues initialValues = new ContentValues();
initialValues.put("Firstname", firstname);
initialValues.put("Surname", surname);
initialValues.put("Username", username);
initialValues.put("Password", md5(password));
initialValues.put("RegisterDate", registerdate);
return db.insert(TABLE_USER, null, initialValues);
}
/**
 * Inserts a Booking entry into the Booking table
 * @return Returns the Booking_ID
 */
public long insertBooking(int amount, String description)
{
ContentValues initialValues = new ContentValues();
initialValues.put("Amount", amount);
initialValues.put("Description", description);
return db.insert(TABLE_BOOKING, null, initialValues);
}
/**
 * Inserts a Planning entry into the Planning table
 * @return Returns the Planning_ID
 */
public long insertPlanning(long budget, long booking, long cycle, String starttime, int count)
{
ContentValues initialValues = new ContentValues();
initialValues.put("Budget", budget);
initialValues.put("Booking", booking);
initialValues.put("Cycle", cycle);
initialValues.put("Starttime", starttime);
initialValues.put("Count", count);
return db.insert(TABLE_PLANNING, null, initialValues);
}
/**
 * Inserts a Category entry into the Category table
 * @return Returns the Category_ID
 */
public long insertCategory(String name, long User_ID)
{
ContentValues initialValues = new ContentValues();
initialValues.put("Name", name);
initialValues.put("User_ID", User_ID);
return db.insert(TABLE_CATEGORY, null, initialValues);
}

/**
 * Inserts a Item entry into the Item table
 * @return Returns the Item_ID
 */
public long insertItem(String name, long category)
{
ContentValues initialValues = new ContentValues();
initialValues.put("Name", name);
initialValues.put("Category", category);
return db.insert(TABLE_ITEM, null, initialValues);
}
/**
 * Inserts a Booking-Item-Relation into the Booking_Item table
 * @return Returns the Booking_Item_ID
 */
public long insertBooking_Item(long item, long booking)
{
ContentValues initialValues = new ContentValues();
initialValues.put("Item", item);
initialValues.put("Booking", booking);
return db.insert(TABLE_BOOKING_ITEM, null, initialValues);
}
/**
 * Inserts a Budget entry into the Budget table
 * @return Returns the Budget_ID
 */
public long insertBudget(String name, int amount, String description, String reason, long user_id, long actual_amount)
{
ContentValues initialValues = new ContentValues();
initialValues.put("Name", name);
initialValues.put("Amount", amount);
initialValues.put("Description", description);
initialValues.put("Reason", reason);
initialValues.put("User_ID", user_id);
initialValues.put("Actual_Amount", actual_amount);
return db.insert(TABLE_BUDGET, null, initialValues);
}
/**
 * Inserts a Job entry into the Job table
 * @return Returns the Job_ID
 */
public long insertJob(String expiring, long amount, long planning_id)
{
ContentValues initialValues = new ContentValues();
initialValues.put("Expiring", expiring);
initialValues.put("Amount", amount);
initialValues.put("Planning", planning_id);
return db.insert(TABLE_JOB, null, initialValues);
}
/**
 * Inserts a Job entry into the JobDone table
 * @return Returns the Job_ID
 */
public long insertJobDone(String expiring, long amount, long planning_id)
{
ContentValues initialValues = new ContentValues();
initialValues.put("Expiring", expiring);
initialValues.put("Amount", amount);
initialValues.put("Planning", planning_id);
return db.insert(TABLE_JOBDONE, null, initialValues);
}

/**
 * Deletes a particular record by ID
 * @return Returns if the Deleting was successful
 */
public boolean delete(String table, long id)
{
	return db.delete(table, table + "_ID=" + id, null) > 0;
}
/**
 * Updates a String value of a particular record
 * @return Returns if the Update was successful
 */
public boolean update(String table, long id, String column, String value)
{
	ContentValues args = new ContentValues();
    args.put(column, value);
    return db.update(table, args, table + "_ID=" + id, null) > 0;

}
/**
 * Updates an Integer value of a particular record
 * @return Returns if the Update was successful
 */
public boolean update(String table, long id, String column, int value)
{
	ContentValues args = new ContentValues();
    args.put(column, value);
    return db.update(table, args, table + "_ID=" + id, null) > 0;

}
/**
 * Updates a Long value of a particular record
 * @return Returns if the Update was successful
 */
public boolean update(String table, long id, String column, long value)
{
	ContentValues args = new ContentValues();
    args.put(column, value);
    return db.update(table, args, table + "_ID=" + id, null) > 0;

}
/**
 * Updates an User
 * @return Returns if the Update was successful
 */
public boolean updateUser(long id, String column, String value)
{
	ContentValues args = new ContentValues();
    args.put(column, value);
    return db.update("UserA", args, "User_ID=" + id, null) > 0;

}
/**
 * Deletes a particular record by the selected key
 * @return Returns if the Deleting was successful
 */
public boolean delete(String table,String key, long id)
{
	return db.delete(table, key + "=" + id, null) > 0;
}
/**
 * Selects all entries of a particular table
 * @return Returns a cursor to the first element of the result
 */
public Cursor SELECTall(String table) throws SQLException
{
	Cursor tCursor =
	db.query(true, table, null, null, null, null, null, null, null);
	if (tCursor != null) {
	tCursor.moveToFirst();
	}
	return tCursor;
}
/**
 * Selects an entries of a particular table by ID
 * @return Returns a cursor to the first element of the result
 */
public Cursor SELECTbyId(String table, long id) throws SQLException
{
	Cursor tCursor =
	db.rawQuery("SELECT * FROM " + table + " WHERE " + table + "_ID=" + id, null);
	if (tCursor != null) {
	tCursor.moveToFirst();
	}
	return tCursor;
}
/**
 * Selects an entries of the User table by ID
 * @return Returns a cursor to the first element of the result
 */
public Cursor SELECTUser(long id) throws SQLException
{
	Cursor tCursor =
	db.query("UserA", null, "User_ID=" + id, null, null, null, null);
	if (tCursor != null) {
	tCursor.moveToFirst();
	}
	return tCursor;
}
/**
 * Selects the given columns of an entries of a particular table by ID
 * @return Returns a cursor to the first element of the result
 */
public Cursor SELECTvaluebyId(String table, String[] column, long id) throws SQLException
{
	Cursor tCursor =
	db.query(true, table, column , table + "_ID= " + id, null, null, null, null, null);
	if (tCursor != null) {
	tCursor.moveToFirst();
	}
	return tCursor;
}
/**
 * Selects an entries of a particular table by key, if the key is an integer
 * @return Returns a cursor to the first element of the result
 */
public Cursor SELECTbyKey(String table, String key, long id) throws SQLException
{
	String ID = String.valueOf(id);
	Cursor tCursor =
	db.query(true, table, null , key + "=" + ID, null, null, null, null, null);
	if (tCursor != null) {
	tCursor.moveToFirst();
	}
	return tCursor;
}
/**
 * Selects an entries of a particular table by key, if the key is a String
 * @return Returns a cursor to the first element of the result
 */
public Cursor SELECTbyString(String table, String key, String str) throws SQLException
{
	Cursor tCursor =
	db.rawQuery("SELECT * FROM " + table + " WHERE " + key + "=" + " '" + str + "'", null);
	if (tCursor != null) {
	tCursor.moveToFirst();
	}
	return tCursor;
}
/**
 * Checks, if the username exists and if the password is correct
 * @return The ID of the User, if he already exists
 */
public long check_login(String username, String password)
{
	long val = -1;
	Cursor pass=SELECTall("UserA");
	
 	if(pass.getCount()>0)
 	{
 		do {System.out.println(pass.getString(3));
 			if(pass.getString(3).equals(username) && pass.getString(4).equals(md5(password))) { 
 				val = pass.getLong(0);
 				break; 
 			}
 		} while (pass.moveToNext());
 	}
	return val;
}

}

